-- 获取物料生产企业、供应商批号、生产许可证号
alter function fun_czly_GetMtlSupplierInfoCp(
    @number varchar(55),
    @org_id int,
    @lot varchar(55),
    @dt datetime -- 生产日期
) returns @result table (
    FProdCpy varchar(255),
    FSupplierLot varchar(55),
    FProdLicenseNo varchar(55)
)
as
begin
    -- 从物料历史获取注册证号
    declare @RegistrationNo varchar(55)
    select @RegistrationNo=FRegistrationNo 
    from dbo.fun_czly_GetMtlHistoryRecord(@number, @org_id, @dt)
    -- 转为医疗类别
    declare @MedicalType varchar(55)
    if @RegistrationNo is not null
    begin
        set @MedicalType=case 
            when @RegistrationNo='' then '5f76bff3c718e6' -- 民用
            when dbo.proc_zjfj_GetZch(@RegistrationNo)='' then '5f76c010c718e8' -- I类 (含有备字的)
            when right(left(dbo.proc_zjfj_GetZch(@RegistrationNo), 5), 1)='1' then '5f76c010c718e8' -- I类
            when right(left(dbo.proc_zjfj_GetZch(@RegistrationNo), 5), 1)='2' then '5f76c01fc718ea' -- II类
            when right(left(dbo.proc_zjfj_GetZch(@RegistrationNo), 5), 1)='3' then '5f76c02bc718ec' -- III类
            when right(left(dbo.proc_zjfj_GetZch(@RegistrationNo), 5), 1)='4' then '5f76c036c718ee' -- IV类
        end
    end

    insert into @result
    select 
        isnull(spl.FName, '上海医疗器械(集团)有限公司手术器械厂') FProdCpy,
        isnull(lm.FSupplierLot, '') FSupplierLot,
        case isnull(@MedicalType, m.F_CZ_YLLB) -- 如果历史记录中没有记录，则使用物料当前的医疗类别
            -- 民用, 生产企业为上手时，使用默认值
            when '5f76bff3c718e6' then case isnull(spl.FName, '') when '' then '' else isnull(sp.F_PAEZ_RecordNo, '') end
            -- I类
            when '5f76c010c718e8' then case isnull(spl.FName, '') when '' then '沪虹食药监械生产备20010031号' else isnull(sp.F_PAEZ_RecordNo, '') end
            -- II、III类
            when '5f76c01fc718ea' then case isnull(spl.FName, '') when '' then '沪食药监械生产许20010031号' else isnull(sp.F_PAEZ_ProdLicenseNo, '') end
            when '5f76c02bc718ec' then case isnull(spl.FName, '') when '' then '沪食药监械生产许20010031号' else isnull(sp.F_PAEZ_ProdLicenseNo, '') end
            else '物料医疗类别错误！'
            end FProdLicenseNo
    from T_BD_MATERIAL m 
    inner join t_bd_MaterialPurchase mp on mp.FMaterialId=m.FMaterialId
    left join (
        select FMaterialId, FMtlLot, FSupplierLot from PAEZ_BD_LotMap where FMtlLot=@lot
        union all 
        select distinct re.FMaterialId, re.FLot_Text, re.FSUPPLYLOT from T_PUR_Receive r
        inner join T_PUR_ReceiveEntry re on re.FID=r.FID
        inner join t_bd_stock sk on sk.FStockID=re.FStockID and sk.FNumber='CK011' -- 经营类成品库
        where re.FLot_Text=@lot and r.FDocumentStatus='C'
    ) lm on lm.FMaterialId=m.FMaterialId
    left join t_BD_Supplier sp on sp.FSupplierId=mp.FDefaultVendorId
    left join t_BD_Supplier_L spl on spl.FSupplierId=sp.FSupplierId
    where m.FNumber=@number and m.FUseOrgId=@org_id

    return

end
-- select * from dbo.fun_czly_GetMtlSupplierInfo('RYC110', 100008, '201101', '2020-12-01')